package com.aimee.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.aimee.entity.User;
import com.aimee.util.Dbutil;

public class LoginDAO {
	//登录
	public User login(int user_id, String user_password) throws SQLException {
		String sql = "SELECT * FROM `user` WHERE user_id=? AND user_password=?";
		Connection conn = Dbutil.getConn();
		QueryRunner runner = new QueryRunner();
		User user = runner.query(conn, sql, new BeanHandler<User>(User.class), user_id,user_password);
		System.out.println(user);
		conn.close();
		return user;
	}
	//查找用户的角色id
	public User findroleid(int user_id) throws SQLException{
		String sql = "SELECT role_id FROM user_role WHERE user_id=?";
		Connection conn = Dbutil.getConn();
		QueryRunner runner = new QueryRunner();
		User role = runner.query(conn, sql, new BeanHandler<User>(User.class), user_id);
		System.out.println("角色号"+role.getRole_id());
//		int role=Integer.parseInt(role_id.toString());
//		System.out.println(role);
		conn.close();
		return role;
	}
	//单个创建用户
	  public int singeradd(User user) throws SQLException {
		   String sql="INSERT INTO `user`(user_id,class_id,role_id,user_name,user_password,user_phone,user_sex) "
		   		+ "VALUES(?,?,?,?,?,?,?)";
		   Connection conn = Dbutil.getConn();
			QueryRunner runner = new QueryRunner();
			int n=0;
			 Object param[]=new Object[7];
			 param[0]=user.getUser_id();
			 param[1]=user.getClass_id();
			 param[2]=user.getRole_id();
			 param[3]=user.getUser_name();
			 param[4]=user.getUser_password();
			 param[5]=user.getUser_phone();
			 param[6]=user.getUser_sex();
			 n=runner.update(conn, sql,param);
			 conn.close();
		   return n;
	   }
// 批量导入增加
   public int add(User user) throws SQLException {
	   String sql="INSERT INTO `user`(class_id,role_id,user_name,user_password,user_phone,user_sex) "
  		+ "VALUES(?,?,?,?,?,?)";
	   Connection conn = Dbutil.getConn();
		QueryRunner runner = new QueryRunner();
		int n=0;
		 Object param[]=new Object[6];
		 param[0]=user.getClass_id();
		 param[1]=user.getRole_id();
		 param[2]=user.getUser_name();
		 param[3]=user.getUser_password();
		 param[4]=user.getUser_phone();
		 param[5]=user.getUser_sex();
		 n=runner.update(conn, sql,param);
		 conn.close();
	   return n;
   }
//   查询用户
   public User findById(int userid) throws SQLException {
		String sql = "SELECT * FROM `user` where user_id=?";
		Connection conn = Dbutil.getConn();
		QueryRunner runner = new QueryRunner();
		User user=runner.query(conn, sql, new BeanHandler<User>(User.class), userid);
		conn.close();
		return user;
   }
// 查询学生人数
   public long count() throws SQLException{
	   String sql = "SELECT count(*) FROM `user` where role_id=3";
		Connection conn = Dbutil.getConn();
		QueryRunner qr = new QueryRunner();
		long count = qr.query(conn, sql, new ScalarHandler<Long>());
		System.out.println(count);
		conn.close();		
        return count;
	   
   }
//   修改用户
//   public int userUpdate(User user) throws SQLException{
//	   String sql="UPDATE `user` SET class_id=?,role_id=?,user_password=?,user_phone=?,user_sex=?,user_wx=? WHERE user_id=?";
//	   Connection conn = Dbutil.getConn();
//		QueryRunner runner = new QueryRunner();
//		int n=0;
//		 Object param[]=new Object[7];
//		 param[0]=user.getClass_id();
//		 param[1]=user.getRole_id();
//		 param[2]=user.getUser_password();
//		 param[3]=user.getUser_phone();
//		 param[4]=user.getUser_sex();
//		 param[5]=user.getUser_wx();
//		 param[6]=user.getUser_id();
//		 System.out.println(param);
//		 n=runner.update(conn, sql, param);
////	        System.out.println("影响了"+n);
//	   return n;
//   }
//   查出所有用户  --要分页查询
   public List show(int page) throws SQLException{
	   String sql="select * from `user` where role_id=3 limit ?,10";
	   Connection conn = Dbutil.getConn();
	   int t=(page-1)*10;
	   QueryRunner runner = new QueryRunner();
	   List list=runner.query(conn, sql,t,new BeanListHandler<User>(User.class));
		conn.close();	   
	   return list;
   }
   
   //删除用户
   public int del(int id) throws Exception{
	   String sql="delete from `user` where user_id=?";
	   Connection conn = Dbutil.getConn();
	   QueryRunner runner = new QueryRunner();
	   int n=runner.update(conn,sql,id);
	   conn.close();
	   System.out.println(n);
	   return n;
	   
   }
   public static void main(String[] args) throws Exception {
		LoginDAO dao=new LoginDAO();
//		User user=dao.findById(2);
//		System.out.println(user);
		User user=new User();
		user=dao.login(100002,"123456");
		System.out.println(user);
//		user.setClass_id(1);
//		user.setUser_password("333333");
//		user.setUser_phone("7777777");
//		user.setUser_wx("bb");
//		user.setUser_desc("2");
//		user.setUser_id(3);
//		int n=dao.update(user);
//		System.out.println("影响行数="+n);
//		for(int i=116;i<200;i++) {
//			user.setUser_id(i);
//			user.setClass_id(1);
//			user.setRole_id(3);
//			user.setUser_name("user_"+i);
//			user.setUser_password("123456");
//			user.setUser_phone("1111111");
//			user.setUser_sex("男");
//			dao.add(user);
//		}
	
//		long n=dao.count();
//		System.out.println(n);
//		
//		System.out.println(dao.show(1));
//		dao.del(10);
	}


}
